package com.carrent.stat.mapper;

import com.carrent.stat.domain.BaseEntity;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 *
 */
@Mapper
@Repository
public interface StatMapper {

    @Select("select address as name,count(*) as value from bus_customer GROUP BY address")
    List<BaseEntity> queryCustomerAreaStat();

    @Select("select opername as name,SUM(price) as value from bus_rent where DATE_FORMAT(createtime,'%Y') = #{year} GROUP BY opername")
    List<BaseEntity> opernameYearGradeStat(String year);

    @Select("select SUM(price) from bus_rent where DATE_FORMAT(begindate,'%Y%m') = CONCAT(#{year},'01')\n" +
            "UNION ALL\n" +
            "select SUM(price) from bus_rent where DATE_FORMAT(begindate,'%Y%m') = CONCAT(#{year},'02')\n" +
            "UNION ALL\n" +
            "select SUM(price) from bus_rent where DATE_FORMAT(begindate,'%Y%m') = CONCAT(#{year},'03')\n" +
            "UNION ALL\n" +
            "select SUM(price) from bus_rent where DATE_FORMAT(begindate,'%Y%m') = CONCAT(#{year},'04')\n" +
            "UNION ALL\n" +
            "select SUM(price) from bus_rent where DATE_FORMAT(begindate,'%Y%m') = CONCAT(#{year},'05')\n" +
            "UNION ALL\n" +
            "select SUM(price) from bus_rent where DATE_FORMAT(begindate,'%Y%m') = CONCAT(#{year},'06')\n" +
            "UNION ALL\n" +
            "select SUM(price) from bus_rent where DATE_FORMAT(begindate,'%Y%m') = CONCAT(#{year},'07')\n" +
            "UNION ALL\n" +
            "select SUM(price) from bus_rent where DATE_FORMAT(begindate,'%Y%m') = CONCAT(#{year},'08')\n" +
            "UNION ALL\n" +
            "select SUM(price) from bus_rent where DATE_FORMAT(begindate,'%Y%m') = CONCAT(#{year},'09')\n" +
            "UNION ALL\n" +
            "select SUM(price) from bus_rent where DATE_FORMAT(begindate,'%Y%m') = CONCAT(#{year},'10')\n" +
            "UNION ALL\n" +
            "select SUM(price) from bus_rent where DATE_FORMAT(begindate,'%Y%m') = CONCAT(#{year},'11')\n" +
            "UNION ALL\n" +
            "select SUM(price) from bus_rent where DATE_FORMAT(begindate,'%Y%m') = CONCAT(#{year},'12')")
    List<Double> companyYearGradeStat(String year);
}
